import pandas as pd

# 读取Excel文件，假设第一行为标题
def read_excel_with_merged_cells(file_path):
    # 使用openpyxl引擎读取Excel文件
    df = pd.read_excel(file_path, engine='openpyxl')
    return df

# 追加内容到表2
def append_to_sheet2(sheet1_df, sheet2_df):
    # 遍历表1的每一行
    for index, row in sheet1_df.iterrows():
        # 获取A列合并单元格的实际值
        file_name = get_merged_cell_value(sheet1_df, index, 'A')
        # 找到表2中A列和B-H列与表1相同的行
        match_rows = sheet2_df[(sheet2_df['A'] == file_name) & sheet2_df.iloc[:, 1:8].isin(row[1:8].tolist()).all(axis=1)]
        # 如果找到匹配的行，则追加内容
        if not match_rows.empty:
            for match_index, match_row in match_rows.iterrows():
                sheet2_df.at[match_index, 'I'] = sheet2_df.at[match_index, 'I'] + row['I']
                sheet2_df.at[match_index, 'J'] = sheet2_df.at[match_index, 'J'] + row['J']

# 处理A列的合并单元格，获取合并单元格的实际值
def get_merged_cell_value(df, row, column_name):
    cell_value = df[column_name].iloc[row]
    if pd.isna(cell_value):  # 如果单元格为空
        # 向上查找第一个非空单元格的值
        for i in range(row, 0, -1):
            if pd.notna(df[column_name].iloc[i-1]):
                return df[column_name].iloc[i-1]
    return cell_value

# 读取两个Excel文件
sheet1_df = read_excel_with_merged_cells('path_to_sheet1.xlsx')
sheet2_df = read_excel_with_merged_cells('path_to_sheet2.xlsx')

# 追加内容
append_to_sheet2(sheet1_df, sheet2_df)

# 保存修改后的表2
sheet2_df.to_excel('path_to_modified_sheet2.xlsx', index=False)